﻿Imports System.Data.OleDb
Imports Infragistics.Win.UltraWinGrid
Imports Infragistics.Win
Imports Crystal.CommonDBManagement.BLL
Imports Crystal.CLL
Imports Crystal.CustomControls
Imports System.Configuration

Public Class UCTemplateMapping
#Region "Declare"
    Private iSourceID As Int16 = 0
    Private iFormatID As Integer = 0
    Public mdtExcelClone As New DataTable
    Public Event ReloadFormatId(ByVal iFormatID As Integer)
    Public isChanged As Boolean = False
    Dim IsFromEDS As Boolean = False
    Public enmSourceTypeCode As SourceTypeCode
    Public gridDataTable As New DataTable
    Public isSelectionChanged As Boolean = False
    Public mblnIsFromUploadEDSScreen As Boolean = False
    Public isBlankMapField As Boolean = True
#End Region

#Region "Properties"

    Public Property FormatID() As Integer
        Get
            Return iFormatID
        End Get
        Set(ByVal value As Integer)
            iFormatID = value
        End Set
    End Property

    Public Property SourceID() As Int16
        Get
            Return iSourceID
        End Get
        Set(ByVal value As Int16)
            iSourceID = value
        End Set
    End Property

#End Region

#Region "Constructors"
    Sub New(ByVal lblnIsFromEDS As Boolean, ByVal strConfiguration As String)
        InitializeComponent()
        IsFromEDS = lblnIsFromEDS
        BLLmdlCommon.strConfiguration = strConfiguration
    End Sub

    Sub New(ByVal pdtExcelClone As DataTable)
        InitializeComponent()
        mdtExcelClone = pdtExcelClone
    End Sub
#End Region

#Region "UCTemplateMapping_Load"

    ''' <summary>
    ''' Load the mapping user control.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub UCTemplateMapping_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        CallOnLoad()
    End Sub

    ''' <summary>
    ''' Call On Load
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub CallOnLoad()
        Try
            If IsFromEDS Then
                If SourceID = CommonCLL.m_strSelectIDValue Then
                    cmbTemplates.DataSource = Nothing
                    grdFormats.DataSource = Nothing
                    Exit Sub
                End If
            End If
            BLLmdlCommon.strConfiguration = CommonCLL.mstrConnectionstring
            'strConfiguration
            InitControls()
            If FormatID <> -1 Then
                isChanged = True
                'cmbTemplates.SelectedValue = FormatID
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

#End Region

#Region "Init"

    ''' <summary>
    ''' To Initialize the controls
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub InitControls()
        Try
            InitCombos()
            SetControls()
            LoadDataBaseField()
            LoadStatusField()
            LoadExcelField()
            LoadFormat(iSourceID)
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub SetControls()
        If IsFromEDS Then
            statInvestorset.Visible = False
            Dim Values(2) As Integer
            If CommonCustomControls.IsValidString(StyleManager.GetStyle("EDSClientBackColor"), Values) Then
                pnlUCTemplateMapping.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
                lblTemplates.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
            End If
            If CommonCustomControls.IsValidString(StyleManager.GetStyle("BlackForeColor"), Values) Then
                lblTemplates.ForeColor = Color.FromArgb(Values(0), Values(1), Values(2))
            End If
        End If
    End Sub

    ''' <summary>
    ''' to set the members of there respective combos
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub InitCombos()
        cmbDataField.DisplayMember = "DisplayName"
        cmbDataField.ValueMember = "DBFieldID"

        cmbStatus.DisplayMember = "Status"
        cmbStatus.ValueMember = "StatusValue"

        cmbTemplates.DisplayMember = "Name"
        cmbTemplates.ValueMember = "ETemplateID"
    End Sub

    ''' <summary>
    ''' To Initlize Data Table "mdtNotImported" that will list the records that could not be imported.
    ''' </summary>
    ''' <remarks></remarks>

    Private Sub LoadDataBaseField()
        Dim eReturnVal As New ExecutionResult
        Dim objDataFields As New cImportInvestors
        Dim dtDataField As New DataTable
        Dim idr As IDataReader = Nothing
        Try
            objDataFields.DbFieldID = -1
            'Retrieve the database fields using Source Type Code.
            idr = objDataFields.SelectDatabaseFields(enmSourceTypeCode.ToString)
            dtDataField.Load(idr)
            mdlCommon.ChangeDataReaderDataTable(dtDataField)
            If BLLmdlCommon.CheckNothingDataTable(dtDataField) Then Exit Sub
            cmbDataField.DataSource = dtDataField
        Catch ex As Exception
            Throw ex
        Finally
            objDataFields = Nothing
            eReturnVal = Nothing
            dtDataField = Nothing
            idr = Nothing
        End Try

    End Sub

    ''' <summary>
    ''' Load the excel data.
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadExcelField()
        Dim dtExcel As New DataTable
        dtExcel.Columns.Add("ExcelField", GetType(String))
        Dim dr1 As DataRow = dtExcel.NewRow
        'Add a blank row to cmbExcelField.
        dr1(0) = ""
        dtExcel.Rows.Add(dr1)
        For i = 0 To mdtExcelClone.Columns.Count - 1
            'For Public, FilingDataCopy column is added in the grdFormat grid, which should be visible in the ExcelData drop down as a row
            If mdtExcelClone.Columns(i).ColumnName.Equals("FilingDateCopy") Then Continue For
            Dim dr As DataRow = dtExcel.NewRow
            'Add a column to cmbExcelField
            dr(0) = mdtExcelClone.Columns(i).ColumnName
            dtExcel.Rows.Add(dr)
        Next
        cmbExcelField.DataSource = dtExcel
        cmbExcelField.Visible = False
    End Sub


    ''' <summary>
    ''' To Load the Status Field for Choosing Status i.e Active or All
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadStatusField()
        Dim tblStatus As New DataTable("Status")
        Try

            tblStatus.Columns.Add("Status")
            tblStatus.Columns.Add("StatusValue")
            Dim dr As DataRow = tblStatus.NewRow
            dr("Status") = "All"
            dr("StatusValue") = "All"
            tblStatus.Rows.Add(dr)
            dr = tblStatus.NewRow
            dr("Status") = "Active"
            dr("StatusValue") = "Active"
            tblStatus.Rows.Add(dr)
            cmbStatus.DataSource = tblStatus
            'cmbStatus.DisplayLayout.Bands(0).Columns(1).Hidden = True
        Catch ex As Exception
            Throw ex
        Finally
            tblStatus = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' To Load bind the All Investor Formats with Format combo
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadFormat(ByVal sourceID As Int16)
        Try
            Dim dataTable As New DataTable
            'dataTable = getFormatName(sourceID)
            Dim lblnHasTemplates As Boolean = False
            dataTable = getFormatName(sourceID)
            If dataTable.Rows.Count > 0 Then
                lblnHasTemplates = True
            End If
            'Add --New-- Option at the first place i.e 0 index. 
            If CommonCLL.DropDownAtFirst(dataTable, cmbTemplates.ValueMember, cmbTemplates.DisplayMember, CommonCLL.m_strNewIDValue, CommonCLL.m_strNewTextValue) = False Then
                CommonCLL.ShowMessage("Error in accessing Format drop down.", MessageBoxIcons.Error)
                Exit Sub
            End If
            cmbTemplates.DataSource = dataTable
            If lblnHasTemplates Then
                If enmSourceTypeCode.ToString <> SourceTypeCode.INV.ToString AndAlso enmSourceTypeCode.ToString <> SourceTypeCode.FND.ToString Then
                    iFormatID = dataTable.Rows(1).Item("ETemplateID")
                End If
                cmbTemplates.SelectedValue = iFormatID
            Else
                cmbTemplates.SelectedIndex = -1
            End If
            'cmbTemplates.DataBind()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region

#Region "Methods"

    ''' <summary>
    ''' To get Format Names from database through dataset
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    ''' 

    Private Function getFormatName(ByVal ESourceID As Int16) As DataTable
        Dim eReturnVal As IDataReader = Nothing
        Dim objInvestorFormat As New cTemplateMapping
        Dim dtSet As New DataTable
        Try
            'Get template name.
            eReturnVal = objInvestorFormat.GetTemplateNames(SourceID)
            dtSet.Load(eReturnVal)
            mdlCommon.ChangeDataReaderDataTable(dtSet)
            Return dtSet
        Catch ex As Exception
            MsgBox(ex.Message)
            Return Nothing
        Finally
            eReturnVal = Nothing
            objInvestorFormat = Nothing
            dtSet = Nothing
        End Try
    End Function

    ''' <summary>
    ''' To fill the Grid for Mapping Process
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub fillgrid()
        Dim dtExcel As New DataTable
        Dim lclscTemplateMapping As New cTemplateMapping
        Try
            'Retrieve the Mapping fields, where template value is selected and SourcetypeCode.
            dtExcel = lclscTemplateMapping.GetTemplateMapping(cmbTemplates.SelectedValue, enmSourceTypeCode.ToString)
            grdFormats.DataSource = dtExcel
            gridDataTable = dtExcel.Copy() ' CType(grdFormats.DataSource, DataTable)
            'grdFormats.DataBind()
            SetGridProperties()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            dtExcel = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Set the style of Grid.
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub SetGridProperties()
        'grdFormats.DisplayLayout.Bands(0).Columns(0).Width = 180
        If grdFormats.DisplayLayout.Bands(0).Columns.Exists("Data Field") Then
            grdFormats.DisplayLayout.Bands(0).Columns("Data Field").Width = 130
        End If
        grdFormats.DisplayLayout.Bands(0).Columns(1).Width = 180
        Me.grdFormats.DisplayLayout.Bands(0).Columns("DBFieldID").Hidden = True
        Me.grdFormats.DisplayLayout.Bands(0).Columns("DBFieldName").Hidden = True
        If grdFormats.DisplayLayout.Bands(0).Columns.Exists("Map Field") Then
            grdFormats.DisplayLayout.Bands(0).Columns("Map Field").CellAppearance.TextHAlign = HAlign.Center
        End If

        grdFormats.DisplayLayout.Bands(0).ColHeadersVisible = False
        grdFormats.DisplayLayout.Override.CellAppearance.BorderAlpha = Alpha.Transparent
        grdFormats.DisplayLayout.Override.RowAppearance.BorderColor = Color.White
        grdFormats.DisplayLayout.Override.RowSelectors = Infragistics.Win.DefaultableBoolean.False
        grdFormats.DisplayLayout.Override.BorderStyleRow = UIElementBorderStyle.None
        grdFormats.DisplayLayout.Override.MinRowHeight = 40
        grdFormats.DisplayLayout.Override.DefaultRowHeight = 40
        grdFormats.DisplayLayout.Override.RowAppearance.TextVAlign = VAlign.Middle

        Dim Values(2) As Integer
        If CommonCustomControls.IsValidString(StyleManager.GetStyle("EDSClientBackColor"), Values) Then
            grdFormats.DisplayLayout.Rows.Band.Columns("Map Field").CellAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
            grdFormats.DisplayLayout.Override.ActiveCellAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
            grdFormats.DisplayLayout.Override.SelectedCellAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
            grdFormats.DisplayLayout.Override.ActiveRowAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
            grdFormats.DisplayLayout.Override.SelectedRowAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
        End If

        If CommonCustomControls.IsValidString(StyleManager.GetStyle("AppThemeBackColor"), Values) Then
            grdFormats.DisplayLayout.Override.RowAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
            grdFormats.DisplayLayout.Override.RowAlternateAppearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
            grdFormats.DisplayLayout.Appearance.BackColor = Color.FromArgb(Values(0), Values(1), Values(2))
        End If

        If CommonCustomControls.IsValidString(StyleManager.GetStyle("AppThemeForeColor"), Values) Then
            grdFormats.DisplayLayout.Rows.Band.Columns("Data Field").CellAppearance.ForeColor = Color.FromArgb(Values(0), Values(1), Values(2))
        End If

        If CommonCustomControls.IsValidString(StyleManager.GetStyle("GridNonSelRowForeColor"), Values) Then
            grdFormats.DisplayLayout.Override.SelectedRowAppearance.ForeColor = Color.FromArgb(Values(0), Values(1), Values(2))
            grdFormats.DisplayLayout.Override.ActiveRowAppearance.ForeColor = Color.FromArgb(Values(0), Values(1), Values(2))
        End If
    End Sub

#End Region

#Region "Mapping Function"

    ''' <summary>
    ''' To Check all Pre-requisites Conditions
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    '''
    Public Function blnValidations() As Boolean
        Dim objinvestorFormat As New cTemplateMapping
        Dim dtMandatoryRegFields As New DataTable
        Dim dtDbfieldsId As New DataTable
        Dim strmandatoryFieldsNames As String
        Dim i As Integer
        Dim str As String = ""
        Dim count As Integer
        Dim alDbFields As New ArrayList
        Dim alColumnMapped As New ArrayList
        Dim FilterQuery As String
        Dim idr As IDataReader = Nothing
        Dim grd As DataTable
        Dim gridData As DataTable
        Dim strFields As String = String.Empty
        Try
            count = 0
            strmandatoryFieldsNames = ""
            Dim drRow As Infragistics.Win.UltraWinGrid.UltraGridRow
            strFields = enmSourceTypeCode.ToString
            If strFields = String.Empty Then
                Return False
            End If
            idr = objinvestorFormat.GetMandatoryFields(strFields)
            dtMandatoryRegFields.Load(idr)
            grd = grdFormats.DataSource
            For i = 0 To dtMandatoryRegFields.Rows.Count - 1
                str = objinvestorFormat.GetInvestorDataBaseFieldName(dtMandatoryRegFields.Rows(i).Item(0))
                FilterQuery = "[Data Field] ='" + Trim(str).Replace("'", "''") + "'"
                'Excel = grd.Select(FilterQuery)
                For Each drRow In grdFormats.Rows
                    If Not String.IsNullOrEmpty(drRow.GetCellValue("Data Field").ToString) Then
                        If drRow.GetCellValue(grdFormats.Rows.Band.Columns(0)) = str Then
                            '''''''''''''''''''''''''''''''''''''''''''''''
                            ''''Commented as requirement is changed, initially user was supposed to select atleast one from InvestorID OR Investor Name.
                            ''''Now the req. is that user must select Investor Name. And the same logic applies to funds.
                            
                            If Not String.IsNullOrEmpty(drRow.GetCellValue(grdFormats.Rows.Band.Columns(1)).ToString()) Then
                                count += 1
                                Exit For
                            Else
                                count = 0
                                Exit For
                            End If
                            'End If
                        End If
                    End If
                Next
                If count = 0 Then
                    If strmandatoryFieldsNames = "" Then
                        strmandatoryFieldsNames &= str
                    End If
                    If strmandatoryFieldsNames = "" Then
                        If IsFromEDS Then
                            MessageBox.Show("Please map mandatory field.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Else
                            statInvestorset.Panels(0).Text = "Please map mandatory field "
                        End If
                    Else
                        If IsFromEDS Then
                            MessageBox.Show("Please map mandatory field '" & strmandatoryFieldsNames & "'.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Else
                            statInvestorset.Panels(0).Text = "Please map mandatory field '" & strmandatoryFieldsNames & "'"
                        End If
                    End If
                    Return False
                    Exit For
                End If
                count = 0
            Next

            ' to check for the duplicate fields
            For Each drRow In grdFormats.Rows
                If Not String.IsNullOrEmpty(drRow.GetCellValue(grdFormats.Rows.Band.Columns(1)).ToString) Then
                    If alColumnMapped.IndexOf(drRow.Cells(1).Text) >= 0 Then
                        If drRow.Cells(1).Text <> "UNMAP" Then
                            If IsFromEDS Then
                                MessageBox.Show("Field '" & drRow.Cells(1).Text & "' matched more than once. Please select another.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
                            Else
                                statInvestorset.Panels(0).Text = "Field '" & drRow.Cells(1).Text & "' matched more than once. Please select another."
                            End If
                            Return False
                        End If
                    Else
                        alColumnMapped.Add(drRow.Cells(1).Text)
                    End If
                End If
            Next
            Dim IsTurnover As Boolean = False
            If enmSourceTypeCode = SourceTypeCode.FND OrElse enmSourceTypeCode = SourceTypeCode.INV Then
                For Each drRow In grdFormats.Rows
                    If Not String.IsNullOrEmpty(drRow.GetCellValue(grdFormats.Rows.Band.Columns(1)).ToString) Then
                        If drRow.Cells("Map Field").Text = "Turnover" Then
                            IsTurnover = True
                        End If
                        If drRow.Cells("Map Field").Text = "Turnover Label" Then
                            IsTurnover = True
                        End If
                    End If
                Next
            End If
            If IsTurnover = False AndAlso (enmSourceTypeCode = SourceTypeCode.FND OrElse enmSourceTypeCode = SourceTypeCode.INV) Then
                If IsFromEDS Then
                    MessageBox.Show("Please select 'Turnover'.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Else
                    statInvestorset.Panels(0).Text = "Please select 'Turnover'"
                End If
                Return False
            End If
            gridData = CType(grdFormats.DataSource, DataTable)

            For i = 0 To gridData.Rows.Count - 1
                For j = 0 To gridData.Columns.Count - 1
                    If gridData.Columns(j).ColumnName.Equals("PublicMatch") = False Then
                        If gridData.Rows(i)(j).ToString <> gridDataTable.Rows(i)(j).ToString Then
                            isSelectionChanged = True
                            Exit For
                        End If
                    End If
                Next
                If isSelectionChanged Then
                    Exit For
                End If
            Next
            For Each drRow In grdFormats.Rows
                If Not String.IsNullOrEmpty(drRow.GetCellValue(grdFormats.Rows.Band.Columns(1)).ToString) Then
                    If objinvestorFormat.CheckExistenceInvestorField(cmbTemplates.SelectedValue) = True Then
                        If isSelectionChanged Then
                            If mblnIsFromUploadEDSScreen Then
                                Return True
                            Else
                                If MessageBox.Show("Do you want to overwrite the change made in template mapping?", mstrMsgBoxCaption, MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = Windows.Forms.DialogResult.OK Then
                                    Return True
                                Else
                                    'isSelectionChanged = False
                                    Return False
                                End If
                            End If
                        End If
                    End If
                End If
            Next

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            'isSelectionChanged = False
            objinvestorFormat = Nothing
            dtMandatoryRegFields = Nothing
            dtDbfieldsId = Nothing
            alDbFields = Nothing
            alColumnMapped = Nothing
            idr = Nothing
            'gridData = Nothing
        End Try
        Return True

    End Function

    ''' <summary>
    ''' To get Mapped fields Result with Status(All,Active)
    ''' </summary>
    ''' <param name="strCol"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    ''' 

    Public Function arrReturnMapFields(ByVal strCol As String) As ArrayList
        Dim arrList As ArrayList = Nothing
        Try
            If cmbTemplates.SelectedIndex <> -1 Then
                Dim eReturnVal As New ExecutionResult
                Dim objinvestorFormat As New cTemplateMapping
                'Dim dtSet As New DataSet
                objinvestorFormat.FormatId = cmbTemplates.SelectedItem.DataValue
                objinvestorFormat.Displayname = Trim(strCol)
                arrList = objinvestorFormat.SelectMapDataBaseFields()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return arrList
    End Function

    Private Function IsChangeValid() As Boolean
        If enmSourceTypeCode = SourceTypeCode.EBL Or enmSourceTypeCode = SourceTypeCode.REG Then
            Return True
        End If
        Return False
    End Function

#End Region

#Region "Button Events"

    ''' <summary>
    ''' To Save new Mapped Criteria under given Format Name
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        mblnIsFromUploadEDSScreen = False
        SaveMapping()
    End Sub

    ''' <summary>
    ''' Save mapping.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveMapping() As Boolean
        If cmbTemplates.SelectedValue Is Nothing Then
            MessageBox.Show("Please select the template.", "Crystal", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Return False
        End If
        Dim objInvestorFormat As New cTemplateMapping
        Dim drRow As Infragistics.Win.UltraWinGrid.UltraGridRow
        Dim intCount As Integer
        Try
            intCount = 0
            'Check if mapping valid selection.
            If blnValidations() Then
                objInvestorFormat.DeleteInvestorMapping(cmbTemplates.SelectedValue)
                For Each drRow In grdFormats.Rows
                    'objInvestorFormat.Status = ""
                    If Not String.IsNullOrEmpty(drRow.GetCellValue(grdFormats.Rows.Band.Columns(1)).ToString) Then
                        objInvestorFormat.FormatId = cmbTemplates.SelectedValue
                        objInvestorFormat.FileColumn = drRow.GetCellValue(grdFormats.Rows.Band.Columns(1))
                        objInvestorFormat.DBFieldId = drRow.GetCellValue(grdFormats.Rows.Band.Columns(2))
                        objInvestorFormat.InsertInvestorFields()
                        intCount += 1
                    Else
                    End If
                Next
                If intCount > 0 Then
                    If cmbTemplates.SelectedValue IsNot DBNull.Value Then
                        RaiseEvent ReloadFormatId(cmbTemplates.SelectedValue)
                    End If
                    FormatID = cmbTemplates.SelectedValue
                    If isSelectionChanged Then
                        If mblnIsFromUploadEDSScreen = False Then
                            MessageBox.Show("Fields mapped successfully.", mstrMsgBoxCaption)
                            Return True
                        End If
                    End If
                    If enmSourceTypeCode = SourceTypeCode.FND OrElse enmSourceTypeCode = SourceTypeCode.INV Then
                        Me.ParentForm.Close()
                    End If
                End If
            Else
                Return False
            End If
            'txtFormat.Visible = True
        Catch ex As Exception
            MessageBox.Show("Error in Saving Details" & ex.Message)
            Return False
        Finally
            drRow = Nothing
            objInvestorFormat = Nothing
            'grdFormats = Nothing
            isSelectionChanged = False
            'gridDataTable = Nothing
        End Try
        Return True
    End Function

    ''' <summary>
    ''' To Close or cancle the current Request
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.ParentForm.Close()
    End Sub

#End Region

#Region "Grid Events"

    ''' <summary>
    ''' Event fires when user select combo value from grid.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub grdFormats_AfterCellListCloseUp(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.CellEventArgs) Handles grdFormats.AfterCellListCloseUp
        If isBlankMapField = False Then
            Dim lstrMapField As String
            Dim columnName As String
            lstrMapField = grdFormats.ActiveRow.Cells("Map Field").Text.ToString()
            If lstrMapField = String.Empty AndAlso (grdFormats.ActiveRow.GetCellValue("DBFieldName").ToString.Equals("MappedInvestorName") = False AndAlso grdFormats.ActiveRow.GetCellValue("DBFieldName").ToString.Equals("MappedFundName") = False) Then
                columnName = grdFormats.ActiveRow.Cells("Data Field").Text.ToString
                MessageBox.Show(MessageManager.GetMessage("CANNOT_UNMAP_DATAFIELD", columnName).Trim(), mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                isBlankMapField = True
                e.Cell.CancelUpdate()
                Exit Sub
            End If
        End If
    End Sub

    ''' <summary>
    ''' Event fire when user click on cell.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub grdFormat_BeforeEnterEditMode(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles grdFormats.BeforeEnterEditMode
        Try
            If grdFormats.ActiveRow Is Nothing Then Exit Sub
            If grdFormats.ActiveRow.Cells Is Nothing Then Exit Sub
            If Me.grdFormats.ActiveCell Is Nothing Then Exit Sub
            grdFormats.ActiveCell.Column.CellActivation = Infragistics.Win.UltraWinGrid.Activation.ActivateOnly
            If Me.grdFormats.ActiveCell.Column.Key = "Map Field" Then
                Me.grdFormats.ActiveCell.Column.CellActivation = Infragistics.Win.UltraWinGrid.Activation.AllowEdit
                If grdFormats.ActiveCell.Value.ToString <> String.Empty And IsChangeValid() Then
                    isBlankMapField = False
                Else
                    isBlankMapField = True
                End If
            ElseIf Me.grdFormats.ActiveCell.Column.Key = "Status" Then
                If grdFormats.ActiveRow.GetCellValue("Map Field") Is System.DBNull.Value Then
                    Me.grdFormats.ActiveCell.Column.CellActivation = Infragistics.Win.UltraWinGrid.Activation.ActivateOnly
                Else
                    If grdFormats.ActiveRow.GetCellText(grdFormats.Rows.Band.Columns("Map Field")).ToString = "Status" Then
                        Me.grdFormats.ActiveCell.Column.CellActivation = Infragistics.Win.UltraWinGrid.Activation.AllowEdit
                    Else
                        Me.grdFormats.ActiveCell.Column.CellActivation = Infragistics.Win.UltraWinGrid.Activation.ActivateOnly
                    End If
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub grdFormat_BeforeRowUpdate(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.CancelableRowEventArgs) Handles grdFormats.BeforeRowUpdate
        Dim ldtGridData As New DataTable
        Dim drRowsField() As DataRow
        Dim lstrMapField As String
        Try
            lstrMapField = grdFormats.ActiveRow.Cells("Map Field").Text.ToString()
            If lstrMapField = String.Empty Then
                Exit Sub
            End If
            If lstrMapField.EndsWith("~") Then
                MessageBox.Show("Special Character '~' are not allowed in  excel column.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                e.Cancel = True
                Exit Sub
            End If
            'If grdFormats.ActiveRow.Cells("Map Field").Text IsNot DBNull.Value Then
            '    If grdFormats.ActiveRow.Cells("Map Field").Text <> "Status" Then
            '        grdFormats.ActiveRow.Cells("Status").Value = System.DBNull.Value
            '    End If
            'End If

            If grdFormats.ActiveRow.Cells("Map Field").Text <> "UNMAP" Then
                ldtGridData = grdFormats.DataSource
                Dim lstrFieldValue As String = grdFormats.ActiveRow.GetCellValue("Map Field")
                'If grdFormats.ActiveRow.Cells("Map Field").Text = "Status" Then  'And grdFormats.ActiveRow.Cells("Status").Text.ToString = ""
                '     e.Cancel = True
                '    Exit Sub
                'End If
                ''Check if selected value is already selected.
                drRowsField = ldtGridData.Select("[Map Field]='" & lstrFieldValue & "'")
                If drRowsField.Length > 0 Then
                    If ldtGridData.Rows.IndexOf(drRowsField(0)) = grdFormats.ActiveRow.Index Then

                    Else
                        'If already selected than show message and exit.
                        MessageBox.Show("Column '" & grdFormats.ActiveRow.Cells("Map Field").Text & "' has been already mapped.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                        e.Cancel = True
                        Exit Sub
                    End If
                End If
                If grdFormats.ActiveCell IsNot Nothing Then
                    Dim str As String = grdFormats.ActiveCell.Value
                End If
                If grdFormats.ActiveRow.Cells("Map Field").Text = "Turnover" OrElse grdFormats.ActiveRow.Cells("Map Field").Text = "Turnover Label" Then
                    drRowsField = ldtGridData.Select("[Map Field]='21' Or [Map Field]= '4'")
                    If drRowsField.Length > 0 Then
                        If grdFormats.ActiveRow.Cells("Map Field").Text = "Turnover" Then
                            MessageBox.Show("Column 'Turnover Label' has been already mapped.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)

                        Else
                            MessageBox.Show("Column 'Turnover' has been already mapped.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)

                        End If
                        e.Cancel = True
                    End If
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally

            ldtGridData = Nothing
            drRowsField = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' for initializing the Grid
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    ''' 

    Private Sub grdFormat_InitializeLayout(ByVal sender As System.Object, ByVal e As Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs) Handles grdFormats.InitializeLayout
        Try
            'e.Layout.Override.CellAppearance.BorderAlpha = Alpha.Transparent
            'e.Layout.Override.RowAppearance.BorderColor = Color.White
            'grdFormats.DisplayLayout.Bands(0).Columns(0).Width = 180
            'grdFormats.DisplayLayout.Bands(0).Columns(1).Width = 180
            'Me.grdFormats.DisplayLayout.Bands(0).Columns("DBFieldID").Hidden = True

            Me.grdFormats.DisplayLayout.Bands(0).Columns("Map Field").EditorComponent = Me.cmbExcelField
            e.Layout.Bands(0).Columns("Map Field").CellAppearance.BorderAlpha = Alpha.UseAlphaLevel
            e.Layout.Bands(0).Columns("Map Field").ButtonDisplayStyle = Infragistics.Win.UltraWinGrid.ButtonDisplayStyle.Always
            Me.grdFormats.DisplayLayout.Bands(0).Columns("Map Field").Style = ColumnStyle.DropDownList

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            fillgrid()
            btnSave.Enabled = True
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

    ''' <summary>
    ''' grdFormats_InitializeRow
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub grdFormats_InitializeRow(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinGrid.InitializeRowEventArgs) Handles grdFormats.InitializeRow
        If e.Row.Cells("Map Field").Column.Key = "Map Field" Then
            If mdtExcelClone.Columns.Contains(e.Row.GetCellValue("Map Field").ToString()) = False Then
                e.Row.Cells("Map Field").Value = String.Empty
            End If
        End If
    End Sub

    ''' <summary>
    ''' grdFormat_KeyDown  Use for press tab in cell.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub grdFormat_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles grdFormats.KeyDown
        If e.KeyCode = Keys.S And e.Modifiers = Keys.Alt Then
            grdFormats.PerformAction(UltraGridAction.NextCellByTab)
        End If
    End Sub

#End Region

#Region "Selecting the Formated Fields and Fill Grid "

    ''' <summary>
    ''' for selecting the Formated Fields 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    ''' 

    Private Sub cmbTemplates_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbTemplates.SelectedValueChanged
        Dim dsReturn As DataSet = Nothing
        Dim data As DataTable = Nothing
        Dim Id As String = String.Empty
        Dim Style As String = String.Empty
        Try
            If cmbTemplates.SelectedValue Is Nothing Then
                Exit Sub
            End If
            'LoadExcelField()
            If cmbTemplates.SelectedValue = CommonCLL.m_strNewIDValue Then
                If isChanged = True Then
                    Dim objAddValue As New CrystalCommon.View.AddValue(enmEntityType.Source.ToString(), LKPMaster.TMPLT, "Template Name")
                    objAddValue.StartPosition = FormStartPosition.CenterScreen
                    objAddValue.ParameterInt = SourceID
                    objAddValue.ShowDialog()
                    Id = objAddValue.ID
                    If Id <> String.Empty Then
                        Dim obj As New cTemplateMapping()
                        dsReturn = obj.GetLatestAddedTemplate(Id)
                        If dsReturn IsNot Nothing Then
                            Style = dsReturn.Tables(0).Rows(0)("Name").ToString()
                            Id = dsReturn.Tables(0).Rows(0)("ETemplateID").ToString()
                            Dim table As DataTable = cmbTemplates.DataSource
                            If table.Columns.Contains("ETemplateID") Then
                                Dim FilterQuery As String
                                Dim Excel() As DataRow
                                FilterQuery = "ETemplateID ='" + Trim(Id).Replace("'", "''") + "'"
                                Excel = table.Select(FilterQuery)
                                If Excel.Length = 0 Then
                                    If CommonCLL.AddRowInDropDownAtLast(table, cmbTemplates.ValueMember, cmbTemplates.DisplayMember, Id, Style) = False Then
                                        CommonCLL.ShowMessage("Error in accessing Template drop down.", MessageBoxIcons.Error)
                                        Exit Sub
                                    End If
                                End If
                                cmbTemplates.SelectedValue = Id
                            End If
                        End If
                    Else
                        cmbTemplates.SelectedValue = FormatID
                    End If

                Else
                    isChanged = True
                End If

            ElseIf cmbTemplates.SelectedValue <> CommonCLL.m_strNewIDValue Then
                If isChanged Then
                    fillgrid()
                    btnSave.Enabled = True
                    FormatID = cmbTemplates.SelectedValue
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

#End Region

End Class
